package com.childenglish.mapper;

import com.childenglish.entity.SysClass;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface SysClassMapper {

    /**
     * 根据班级ID查询班级
     */
    @Select("SELECT * FROM sys_class WHERE class_id = #{classId}")
    @Results({
            @Result(property = "classId", column = "class_id"),
            @Result(property = "className", column = "class_name"),
            @Result(property = "classCategory", column = "class_category"),
            @Result(property = "gradeLevel", column = "grade_level"),
            @Result(property = "ageRange", column = "age_range"),
            @Result(property = "maxStudents", column = "max_students"),
            @Result(property = "currentStudents", column = "current_students"),
            @Result(property = "teacherId", column = "teacher_id"),
            @Result(property = "classDescription", column = "class_description"),
            @Result(property = "status", column = "status"),
            @Result(property = "createTime", column = "create_time"),
            @Result(property = "updateTime", column = "update_time")
    })
    SysClass findById(@Param("classId") Long classId);

    /**
     * 根据年级级别查询班级列表
     */
    @Select("SELECT * FROM sys_class WHERE grade_level = #{gradeLevel} AND status = '0' ORDER BY class_name")
    @Results({
            @Result(property = "classId", column = "class_id"),
            @Result(property = "className", column = "class_name"),
            @Result(property = "classCategory", column = "class_category"),
            @Result(property = "gradeLevel", column = "grade_level"),
            @Result(property = "ageRange", column = "age_range"),
            @Result(property = "maxStudents", column = "max_students"),
            @Result(property = "currentStudents", column = "current_students"),
            @Result(property = "teacherId", column = "teacher_id"),
            @Result(property = "classDescription", column = "class_description"),
            @Result(property = "status", column = "status"),
            @Result(property = "createTime", column = "create_time"),
            @Result(property = "updateTime", column = "update_time")
    })
    List<SysClass> findByGradeLevel(@Param("gradeLevel") String gradeLevel);

    /**
     * 根据年龄范围查询班级列表
     */
    @Select("SELECT * FROM sys_class WHERE age_range = #{ageRange} AND status = '0' ORDER BY class_name")
    @Results({
            @Result(property = "classId", column = "class_id"),
            @Result(property = "className", column = "class_name"),
            @Result(property = "classCategory", column = "class_category"),
            @Result(property = "gradeLevel", column = "grade_level"),
            @Result(property = "ageRange", column = "age_range"),
            @Result(property = "maxStudents", column = "max_students"),
            @Result(property = "currentStudents", column = "current_students"),
            @Result(property = "teacherId", column = "teacher_id"),
            @Result(property = "classDescription", column = "class_description"),
            @Result(property = "status", column = "status"),
            @Result(property = "createTime", column = "create_time"),
            @Result(property = "updateTime", column = "update_time")
    })
    List<SysClass> findByAgeRange(@Param("ageRange") String ageRange);

    /**
     * 根据教师ID查询班级列表
     */
    @Select("SELECT * FROM sys_class WHERE teacher_id = #{teacherId} AND status = '0' ORDER BY class_category, class_name")
    @Results({
            @Result(property = "classId", column = "class_id"),
            @Result(property = "className", column = "class_name"),
            @Result(property = "classCategory", column = "class_category"),
            @Result(property = "gradeLevel", column = "grade_level"),
            @Result(property = "ageRange", column = "age_range"),
            @Result(property = "maxStudents", column = "max_students"),
            @Result(property = "currentStudents", column = "current_students"),
            @Result(property = "teacherId", column = "teacher_id"),
            @Result(property = "classDescription", column = "class_description"),
            @Result(property = "status", column = "status"),
            @Result(property = "createTime", column = "create_time"),
            @Result(property = "updateTime", column = "update_time")
    })
    List<SysClass> findByTeacherId(@Param("teacherId") Long teacherId);

    /**
     * 查询所有正常状态的班级
     */
    @Select("SELECT * FROM sys_class WHERE status = '0' ORDER BY class_category, class_name")
    @Results({
            @Result(property = "classId", column = "class_id"),
            @Result(property = "className", column = "class_name"),
            @Result(property = "classCategory", column = "class_category"),
            @Result(property = "gradeLevel", column = "grade_level"),
            @Result(property = "ageRange", column = "age_range"),
            @Result(property = "maxStudents", column = "max_students"),
            @Result(property = "currentStudents", column = "current_students"),
            @Result(property = "teacherId", column = "teacher_id"),
            @Result(property = "classDescription", column = "class_description"),
            @Result(property = "status", column = "status"),
            @Result(property = "createTime", column = "create_time"),
            @Result(property = "updateTime", column = "update_time")
    })
    List<SysClass> findAllActive();

    /**
     * 插入新班级
     */
    @Insert("INSERT INTO sys_class (class_name, class_category, grade_level, age_range, max_students, current_students, " +
            "teacher_id, class_description, status, create_time, update_time) " +
            "VALUES (#{className}, #{classCategory}, #{gradeLevel}, #{ageRange}, #{maxStudents}, #{currentStudents}, " +
            "#{teacherId, jdbcType=BIGINT}, #{classDescription}, #{status}, #{createTime}, #{updateTime})")
    @Options(useGeneratedKeys = true, keyProperty = "classId")
    int insert(SysClass sysClass);

    /**
     * 更新班级信息
     */
    @Update("UPDATE sys_class SET class_name = #{className}, class_category = #{classCategory}, grade_level = #{gradeLevel}, " +
            "age_range = #{ageRange}, max_students = #{maxStudents}, current_students = #{currentStudents}, " +
            "teacher_id = #{teacherId, jdbcType=BIGINT}, class_description = #{classDescription}, status = #{status}, " +
            "update_time = #{updateTime} WHERE class_id = #{classId}")
    int update(SysClass sysClass);

    /**
     * 更新班级当前学生数
     */
    @Update("UPDATE sys_class SET current_students = #{currentStudents}, update_time = NOW() " +
            "WHERE class_id = #{classId}")
    int updateCurrentStudents(@Param("classId") Long classId, @Param("currentStudents") Integer currentStudents);

    /**
     * 删除班级（软删除，更新状态为停用）
     */
    @Update("UPDATE sys_class SET status = '1', update_time = NOW() WHERE class_id = #{classId}")
    int deleteById(@Param("classId") Long classId);

    /**
     * 根据年龄自动分配班级（选择学生数最少的班级）
     */
    @Select("SELECT * FROM sys_class WHERE age_range = #{ageRange} AND status = '0' " +
            "AND current_students < max_students ORDER BY current_students ASC, class_id ASC LIMIT 1")
    @Results({
            @Result(property = "classId", column = "class_id"),
            @Result(property = "className", column = "class_name"),
            @Result(property = "classCategory", column = "class_category"),
            @Result(property = "gradeLevel", column = "grade_level"),
            @Result(property = "ageRange", column = "age_range"),
            @Result(property = "maxStudents", column = "max_students"),
            @Result(property = "currentStudents", column = "current_students"),
            @Result(property = "teacherId", column = "teacher_id"),
            @Result(property = "classDescription", column = "class_description"),
            @Result(property = "status", column = "status"),
            @Result(property = "createTime", column = "create_time"),
            @Result(property = "updateTime", column = "update_time")
    })
    SysClass findAvailableClassByAgeRange(@Param("ageRange") String ageRange);
}

